﻿CREATE PROCEDURE [dbo].[IDaoUser_GetAll]
AS




SELECT
	 U.Id
	,U.Name
	,U.Mail
	,U.[Role]
	,R.Name	AS RoleName
	,SUBSTRING(GROUPS.Names, 1, LEN(GROUPS.Names) -1)	AS GroupNames
	,SUBSTRING(GROUPSID.Ids, 1, LEN(GROUPSID.Ids) -1)	AS GroupIds
FROM	tbUser	U
	JOIN	tbRole	R
		ON	R.Id = U.Role
	LEFT JOIN	(
		SELECT
			UG.Id
			,(
				SELECT
					Name + ', '
				FROM	tbUserGroup	UGXML
					JOIN	tbGroup	G
						ON	G.Id = UGXML.[Group]
				FOR XML PATH('')
			)	AS Names
		FROM	tbUserGroup	UG
		GROUP BY
			UG.Id
		)	GROUPS
		ON	GROUPS.Id = U.Id
	LEFT JOIN	(
		SELECT
			UG.Id
			,(
				SELECT
					CONVERT(nvarchar(100), [Group]) + ','
				FROM	tbUserGroup	UGXML
					JOIN	tbGroup	G
						ON	G.Id = UGXML.[Group]
				FOR XML PATH('')
			)	AS Ids
		FROM	tbUserGroup	UG
		GROUP BY
			UG.Id
		)	GROUPSID
		ON	GROUPSID.Id = U.Id




RETURN 0
